
[dbo].[vCustomerExperiences]
CREATE VIEW [dbo].[vCustomerExperiences]
AS
SELECT
[OfferedCustomerExperienceKey] AS CustomerExperienceKey,
[ExperienceDefinitionName] AS ExperienceName,
[NumberOfUnits] AS Units,
CASE WHEN [AllowChangesFlag] = '1' THEN [OfferedCustomerExperienceDate] ELSE [OfferingDate] END AS DateExp,
[ProgramKey],
[ProgramName] AS Program,
[LocationName] AS Location,
[RequiresGradeFlag],
[OfferedCustomerExperienceGrade] AS Grade
FROM
[dbo].[vOfferedCustomerExperience]
UNION ALL
SELECT
[UnofferedCustomerExperienceKey] AS CustomerExperienceKey,
[ExperienceDefinitionName] AS ExperienceName,
[NumberOfUnits] AS Units,
[ExperienceDefinitionDate] AS DateExp,
[ProgramKey],
[ProgramName] AS Program,
[ExperienceDefinitionLocation] AS Location,
[RequiresGradeFlag],
[UnofferedCustomerExperienceGrade] AS Grade
FROM
[dbo].[vUnofferedCustomerExperience]
UNION ALL
SELECT
[UserDefinedCustomerExperienceKey] AS CustomerExperienceKey,
[UserDefinedCustomerExperienceName] AS ExperienceName,
[UserDefinedCustomerExperienceUnits] AS Units,
[CompletionDate] AS DateExp,
[ProgramKey],
[ProgramName] AS Program,
[UserDefinedCustomerExperienceLocation] AS Location,
[RequiresGradeFlag],
[Grade]
FROM
[dbo].[vUserDefinedCustomerExperience]
UNION ALL
SELECT
[OfferedCustomerExperienceKey] AS CustomerExperienceKey,
[CertificationModuleName] AS ExperienceName,
[NumberOfUnits] AS Units,
[OfferingDate] AS DateExp,
[CertificationProgramKey] AS ProgramKey,
[CertificationProgramName] AS Program,
[LocationName] AS Location,
[RequiresGradeFlag],
[OfferedCustomerExperienceGrade] AS Grade
FROM
[dbo].[vModuleOfferedCustomerExperience]
GO